package com.myres.db;

import com.myres.db.ConnectionPool;
import com.myres.model.Product;
import com.myres.model.Score;
import com.myres.model.User;
import com.myres.util.UIDTool;

import java.util.Map;
import java.util.TreeMap;
import java.util.List;
import java.util.ArrayList;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
public class ModelService {
	public Product getProductById(int id){
		Connection con=null;
   	    PreparedStatement pstmt=null;
   	    ResultSet rs=null;
   	    try{
   	    	con=ConnectionPool.getPool().getConnection();
   	    	pstmt=con.prepareStatement(
   	    	"select * from res_products where _id=?");
   	    	pstmt.setInt(1, id);
   	        rs=pstmt.executeQuery();
   	        return Product.populate(rs).get(0);
   	    }catch(SQLException e){
   	    	e.printStackTrace();
   	    	return null;
   	    }finally{
   	    	try{
   	    	con.close();
   	    	}catch(Exception e){
   	    		
   	    	}
   	    }
	}
	public int[] insertScore(Score[] scores){
		Connection con=null;
   	    PreparedStatement pstmt=null;
   	    ResultSet rs=null;
   	    try{
   	    	con=ConnectionPool.getPool().getConnection();
   	    	pstmt=con.prepareStatement(
   	    	"insert into res_scores(_order_no,_score,_user_id,_note,_reason)values(?,?,?,?,?)");
   	    	for(Score score:scores){
   	    	pstmt.setString(1, score.getOno());
   	    	pstmt.setInt(2, score.getScore());
   	    	pstmt.setInt(3, score.getUid());
   	    	pstmt.setString(4,score.getNote());
   	    	pstmt.setInt(5, score.getReason());
   	    	pstmt.addBatch();
   	    	}
   	        return pstmt.executeBatch();
   	    }catch(SQLException e){
   	    	e.printStackTrace();
   	    	return null;
   	    }finally{
   	    	try{
   	    	con.close();
   	    	}catch(Exception e){
   	    		
   	    	}
   	    }
	}
	public int[] insertScore(List<Score> scores){
		Connection con=null;
   	    PreparedStatement pstmt=null;
   	    ResultSet rs=null;
   	    try{
   	    	con=ConnectionPool.getPool().getConnection();
   	    	pstmt=con.prepareStatement(
   	    	"insert into res_scores(_order_no,_score,_user_id,_note,_reason)values(?,?,?,?,?)");
   	    	for(Score score:scores){
   	    	pstmt.setString(1, score.getOno());
   	    	pstmt.setInt(2, score.getScore());
   	    	pstmt.setInt(3, score.getUid());
   	    	pstmt.setString(4,score.getNote());
   	    	pstmt.setInt(5, score.getReason());
   	    	pstmt.addBatch();
   	    	}
   	        return pstmt.executeBatch();
   	    }catch(SQLException e){
   	    	e.printStackTrace();
   	    	return null;
   	    }finally{
   	    	try{
   	    	con.close();
   	    	}catch(Exception e){
   	    		
   	    	}
   	    }
   	    }
   	 public int getScore(int uid){
   		Connection con=null;
   	    PreparedStatement pstmt=null;
   	    ResultSet rs=null;
   	    try{
   	    	con=ConnectionPool.getPool().getConnection();
   	    	pstmt=con.prepareStatement(
   	    	"select sum(_score) from res_scores where _user_id =?");
   	    	pstmt.setInt(1, uid);
   	        rs= pstmt.executeQuery();
   	        if(rs.next()){
   	        	return rs.getInt(1);
   	        }
   	        return -1;
   	    }catch(SQLException e){
   	    	e.printStackTrace();
   	    	return -1;
   	    }finally{
   	    	try{
   	    	con.close();
   	    	}catch(Exception e){
   	    		
   	    	}
   	    }
   	 }
   	 public synchronized int isSendScore(int uid){
   		Connection con=null;
   	    PreparedStatement pstmt=null;
   	    ResultSet rs=null;
   	    try{
   	    	con=ConnectionPool.getPool().getConnection();
   	    	pstmt=con.prepareStatement(
   	    	"select count(_id) from res_scores where _user_id=? and _add_time>? and _reason=?");
   	    	
   	    	pstmt.setInt(1, uid);
   	    	Timestamp time=new Timestamp(System.currentTimeMillis());
   	    	time.setHours(0);
   	    	time.setMinutes(0);
   	    	time.setMinutes(0);
   	    	time.setSeconds(0);
   	    	time.setNanos(0);
   	    	pstmt.setTimestamp(2, time);
   	    	pstmt.setInt(3, Score.TYPE_VISIT);
   	        rs= pstmt.executeQuery();
   	        if(rs.next()){
   	        	int sent= rs.getInt(1);
   	        	System.out.println("�Ѿ��͹�����"+sent+"��");
   	        	return sent;
   	        }
   	        return -1;
   	    }catch(SQLException e){
   	    	e.printStackTrace();
   	    	return -1;
   	    }finally{
   	    	try{
   	    	con.close();
   	    	}catch(Exception e){
   	    		
   	    	}
   	    }
   		 
   	 }
   	 public List<User> getUserForSMS(){
   		Connection con=null;
   	    PreparedStatement pstmt=null;
   	    ResultSet rs=null;
   	    List<User> users=new ArrayList<User>();
   	    try{
   	    	con=ConnectionPool.getPool().getConnection();
   	    	pstmt=con.prepareStatement(
   	    	"select _id, _name,_mobile from res_users where _mobile is not null  and length(_mobile)=11 order by _id");
   	        rs= pstmt.executeQuery();
   	        while(rs.next()){
   	        	User user=new User();
   	        	user.setId(rs.getInt(1));
   	        	user.setName(rs.getString(2));
   	        	user.setMobile(rs.getString(3));
   	        	user.setSmsToken(UIDTool.generateToken(10));
   	        	users.add(user);
   	        }
   	        return users;
   	    }catch(SQLException e){
   	    	e.printStackTrace();
   	    	return null;
   	    }finally{
   	    	try{
   	    	con.close();
   	    	}catch(Exception e){
   	    		
   	    	}
   	    }
   	 }
   	public List<User> checkUser(int id){
   		Connection con=null;
   	    PreparedStatement pstmt=null;
   	    ResultSet rs=null;
   	    List<User> users=new ArrayList<User>();
   	    try{
   	    	con=ConnectionPool.getPool().getConnection();
   	    	pstmt=con.prepareStatement(
   	    	"select * from res_users where _id>? and _mobile is not null  and length(_mobile) >10 order by _id");
   	    	pstmt.setInt(1, id);
   	    
   	        rs= pstmt.executeQuery();
   	        return User.populate(rs);
//   	        while(rs.next()){
//   	        	User user=new User();
//   	        	user.setName(rs.getString(1));
//   	        	user.setMobile(rs.getString(2));
//   	        	users.add(user);
//   	        }
   	        //return users;
   	    }catch(SQLException e){
   	    	e.printStackTrace();
   	    	return null;
   	    }finally{
   	    	try{
   	    	con.close();
   	    	}catch(Exception e){
   	    		
   	    	}
   	    }
   	}
	public List<User> getAllUser(){
		Connection con=null;
   	    PreparedStatement pstmt=null;
   	    ResultSet rs=null;
   	    List<User> users=new ArrayList<User>();
   	    try{
   	    	con=ConnectionPool.getPool().getConnection();
   	    	pstmt=con.prepareStatement(
   	    	"select _name,_mobile from res_users where _mobile is not null  and length(_mobile) >10 order by _id");
   	        rs= pstmt.executeQuery();
   	        while(rs.next()){
   	        	User user=new User();
   	        	user.setName(rs.getString(1));
   	        	user.setMobile(rs.getString(2));
   	        	users.add(user);
   	        }
   	        return users;
   	    }catch(SQLException e){
   	    	e.printStackTrace();
   	    	return null;
   	    }finally{
   	    	try{
   	    	con.close();
   	    	}catch(Exception e){
   	    		
   	    	}
   	    }
	}

}
